﻿using EagleProject.Models;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace EagleProject.LinQDataContext
{
    public class DatabaseController
    {
        private static EagleDataContextDataContext GetDB()
        {
            var db = new LinQDataContext.EagleDataContextDataContext();
            return db;
        }

        public static List<State> GetStates()
        {
            var db = GetDB();
            var data = from item in db.States
                       select item;
            return data.ToList();
        }

        public static List<Country> GetCountries()
        {
            var db = GetDB();
            var data = from item in db.Countries
                       select item;
            return data.ToList();
        }

        public static List<LocalGovArea> GetLGAs()
        {
            var db = GetDB();
            var data = from item in db.LocalGovAreas
                       select item;
            return data.ToList();
        }

        public static List<LocalGovArea> GetLGAsByState(int stateId)
        {
            var db = GetDB();
            var data = from item in db.LocalGovAreas
                       where item.StateID == stateId
                       select item;
            return data.ToList();
        }

        public static AccidentRecord GetAccidentRecordByID(int id)
        {
            var db = GetDB();
            var data = db.AccidentRecords.Single(m => m.ID == id);
            return data;
        }

        public static void CreateAccidentRecord(AccidentRecord model)
        {
            var db = GetDB();
            model.ID = 0;
            db.AccidentRecords.InsertOnSubmit(model);
            db.SubmitChanges();
        }

        public static void EditAccidentRecord(AccidentRecord model)
        {
            var db = GetDB();
            var data = db.AccidentRecords.Single(m => m.ID == model.ID);

            data.AccidentPictureURL = model.AccidentPictureURL;
            data.AccidentType = model.AccidentType;
            data.Descriptions = model.Descriptions;
            data.LevelOfSeverity = model.LevelOfSeverity;
            data.LGAID = model.LGAID;
            data.Location = model.Location;

            db.SubmitChanges();
        }

        private static CommonUser GenerateCommonUserInformation()
        {
            CommonUser u = new CommonUser
            {
                Passwords = "123456"
            };
            return u;
        }

        public static void CreateConsultantCommonUser(FormConsultantModel model)
        {
            var db = GetDB();

            CommonUser u = GenerateCommonUserInformation();
            u.Username = model.UserName;
            u.UserType = "Consultant";
            db.CommonUsers.InsertOnSubmit(u);

            Consultant c = new Consultant
            {
                Addresses = model.Addresses,
                AdminID = model.AdminID,
                CommonUser1 = u,
                City = model.City,
                Email = model.Email,
                FirstName = model.FirstName,
                MiddleName = model.MiddleName,
                Lastname = model.Lastname,
                LGAID = model.LGAID
            };
            db.Consultants.InsertOnSubmit(c);

            db.SubmitChanges();
        }

        public static bool IsContainUserName(string userName)
        {
            var db = GetDB();
            bool isContain = (from data in db.CommonUsers
                              where data.Username.Equals(userName)
                              select data).Any();
            return isContain;
        }

        public static CommonUser CreateSubscriberCommonUser(RegisterModel model)
        {
            if (IsContainUserName(model.UserName)) { return null; }

            var db = GetDB();
            var user = new CommonUser()
            {
                Username = model.UserName,
                Passwords = model.Password,
                UserType = "Subscriber"
            };

            db.CommonUsers.InsertOnSubmit(user);

            Subcriber s = new Subcriber()
            {
                UniqueCode = System.Guid.NewGuid(),
                CommonUser1 = user
            };

            db.SubmitChanges();
            return user;
        }

        public static void CreateAgentCommonUser(FormAgentModel model)
        {
            var db = GetDB();

            CommonUser u = GenerateCommonUserInformation();
            u.Username = model.UserName;
            u.UserType = "Agent";
            db.CommonUsers.InsertOnSubmit(u);

            Agent c = new Agent
            {
                Addresses = model.Addresses,
                ConsultantID = model.ConsultantID,
                CommonUser1 = u,
                City = model.City,
                Email = model.Email,
                FirstName = model.FirstName,
                Lastname = model.Lastname,
                LGAID = model.LGAID,
                Guarantor_Name = model.Guarantor_Name,
                Guarantor_Address = model.Guarantor_Address,
                Guarantor_ContactNumber1 = model.Guarantor_ContactNumber1,
                Guarantor_ContactNumber2 = model.Guarantor_ContactNumber2
            };
            db.Agents.InsertOnSubmit(c);

            db.SubmitChanges();
        }

        public static CommonUser GetCommonUser(string userName, string password)
        {
            var db = GetDB();
            var users = (from data in db.CommonUsers
                         where data.Username.Equals(userName)
                         select data);
            if (!users.Any()) return null;

            var user = users.First();
            if (user.Passwords.Equals(password))
            {
                return user;
            }
            else return null;
        }

        public static void CreateSubscriberReg(CommonSubscriberReg form, int cardType)
        {
            var db = GetDB();

            form.RegType = cardType;
            form.UniqueCode = System.Guid.NewGuid();

            db.CommonSubscriberRegs.InsertOnSubmit(form);

            switch (cardType)
            {
                case 1: db.RegularSubscriberRegs.InsertOnSubmit(new RegularSubscriberReg { CommonSubscriberReg = form }); break;
                case 2: db.ProcessedSubscriberRegs.InsertOnSubmit(new ProcessedSubscriberReg { CommonSubscriberReg = form }); break;
                case 3: db.SpecialSubscriberRegs.InsertOnSubmit(new SpecialSubscriberReg { CommonSubscriberReg = form }); break;
                case 4: db.FirstClassCitizenRegs.InsertOnSubmit(new FirstClassCitizenReg { CommonSubscriberReg = form }); break;
            }

            db.SubmitChanges();
        }

        public static bool CreateSubscriberRegSubscriberCommonUser(FormAgentCreateSubscriber model, int cardType, int agentID)
        {
            if (IsContainUserName(model.CommonUser.Username)) return false;

            var db = GetDB();

            var commonUser = GenerateCommonUserInformation();
            commonUser.Username = model.CommonUser.Username;
            commonUser.UserType = "Subscriber";
            db.CommonUsers.InsertOnSubmit(commonUser);

            var subscriber = new Subcriber()
            {
                CommonUser1 = commonUser,
                AgentID = agentID,
                UniqueCode = System.Guid.NewGuid()
            };
            db.Subcribers.InsertOnSubmit(subscriber);

            var form = model.RegistrationForm;
            form.RegType = cardType;
            switch (cardType)
            {
                case 1: db.RegularSubscriberRegs.InsertOnSubmit(new RegularSubscriberReg { CommonSubscriberReg = form }); break;
                case 2: db.ProcessedSubscriberRegs.InsertOnSubmit(new ProcessedSubscriberReg { CommonSubscriberReg = form }); break;
                case 3: db.SpecialSubscriberRegs.InsertOnSubmit(new SpecialSubscriberReg { CommonSubscriberReg = form }); break;
                case 4: db.FirstClassCitizenRegs.InsertOnSubmit(new FirstClassCitizenReg { CommonSubscriberReg = form }); break;
            }
            db.CommonSubscriberRegs.InsertOnSubmit(form);

            db.SubmitChanges();

            return true;
        }

        public static IQueryable<Subcriber> GetAllSubscriberCreatedByMe(int myID)
        {
            var db = GetDB();
            var datas = from data in db.Subcribers
                        where data.AgentID == myID
                        select data;
            return datas;
        }

        public static IQueryable<CommonSubscriberReg> GetRegisterFormByMe(int myID)
        {
            var db = GetDB();
            var datas = from data in db.CommonSubscriberRegs
                        where data.CommonUser1.ID == myID
                        select data;
            return datas;
        }

        public static CommonSubscriberReg GetRegisterFormByMeById(int myID, int regId)
        {
            var db = GetDB();
            var data = db.CommonSubscriberRegs.Single(m => m.ID == regId);
            if (data == null) return null;
            if (data.CommonUser != myID) { return null; }
            return data;
        }

        public static IQueryable<CommonSubscriberReg> GetAllCommonSubscriberReg()
        {
            return GetDB().CommonSubscriberRegs;
        }

        public static IQueryable<Partner> GetPartners()
        {
            var db = GetDB();

            var datas = from data in db.Partners
                        select data;

            return datas;
        }

        public static void CreateEvent(Eventt model)
        {
            var db = GetDB();
            db.Eventts.InsertOnSubmit(model);
            db.SubmitChanges();
        }

        public static Eventt GetEventByID(int id)
        {
            var db = GetDB();
            var e = db.Eventts.Single(m => m.ID == id);
            return e;
        }

        public static void EditEvent(Eventt model)
        {
            var db = GetDB();
            var data = db.Eventts.Single(m => m.ID == model.ID);

            data.ID = model.ID;
            data.AdminID = model.AdminID;
            data.PartnerID = model.PartnerID;
            data.Name = model.Name;
            data.Location = model.Location;

            db.SubmitChanges();
        }

        public static IQueryable<Eventt> GetAllEvents()
        {
            return GetDB().Eventts;
        }

        public static IQueryable<AccidentRecord> GetAllAccidentRecords()
        {
            return GetDB().AccidentRecords;
        }

        public static IQueryable<CommonUser> GetAllCommonUsers()
        {
            return GetDB().CommonUsers;
        }

        public static bool UserChangePassword(int userID, string oldPassword, string newPassword)
        {
            var db = GetDB();

            var user = db.CommonUsers.Single(m => m.ID == userID);

            if (user == null || !user.Passwords.Equals(oldPassword))
            {
                return false;
            }
            else
            {
                user.Passwords = newPassword;
                db.SubmitChanges();
                return true;
            }
        }

        public static CommonUser GetCommonUser(int id)
        {
            var db = GetDB();

            var user = db.CommonUsers.Single(m => m.ID == id);

            return user;
        }

        public static void EditUserProfile(CommonUser model)
        {
            var db = GetDB();

            var user = db.CommonUsers.Single(m => m.ID == model.ID);
            user.PictureUrl = model.PictureUrl;

            db.SubmitChanges();
        }

        public static void CreateContactInfo(ContactInfoModel model)
        {
            var db = GetDB();

            db.ContactInfos.InsertOnSubmit(new ContactInfo
            {
                Name = model.Name,
                Email = model.Email,
                Subjects = model.Subject,
                Messagess = model.Message
            });

            db.SubmitChanges();
        }

        public static IQueryable<JobApplication> GetAllJobApplications()
        {
            return GetDB().JobApplications;
        }

        public static void CreateJobApplication(JobApplication model)
        {
            var db = GetDB();

            db.JobApplications.InsertOnSubmit(model);

            db.SubmitChanges();
        }

        public static JobApplication GetApplicationByID(int id)
        {
            var db = GetDB();
            var data = db.JobApplications.Single(m => m.ID == id);
            return data;
        }

        public static void CreatePartnerCommonUser(FormPartnerModel model)
        {
            var db = GetDB();

            CommonUser u = GenerateCommonUserInformation();
            u.Username = model.UserName;
            u.UserType = "Partner";
            db.CommonUsers.InsertOnSubmit(u);

            Partner c = new Partner
            {
                Name = model.Name,
                Location = model.Location,
                ContactNumber1 = model.ContactNumber1,
                ContactNumber2 = model.ContactNumber2,
                ContactEmail = model.ContactEmail,
                AdminID = model.AdminID,
                CommonUser1 = u
            };
            db.Partners.InsertOnSubmit(c);

            db.SubmitChanges();
        }

        public static Partner GetPartnerByID(int id)
        {
            var db = GetDB();
            var data = db.Partners.Single(m => m.ID == id);
            return data;
        }

        public static IQueryable<Consultant> GetConsultants()
        {
            return GetDB().Consultants;
        }

        public static IQueryable<Agent> GetAgents()
        {
            return GetDB().Agents;
        }

        public static void CreateHumanResourceCommonUser(FormHumanResourceModel model)
        {
            var db = GetDB();

            CommonUser u = GenerateCommonUserInformation();
            u.Username = model.UserName;
            u.UserType = "HumanResource";
            db.CommonUsers.InsertOnSubmit(u);

            HumanResource c = new HumanResource
            {
                AdminID = model.AdminID,
                FirstName = model.FirstName,
                Lastname = model.Lastname,
                Addresses = model.Addresses,
                City = model.City,
                Email = model.Email,
                LGAID = model.LGAID,
                CommonUser1 = u
            };
            db.HumanResources.InsertOnSubmit(c);

            db.SubmitChanges();
        }

        public static IQueryable<Subcriber> GetAllSubscribers() { return GetDB().Subcribers; }

        public static Subcriber GetSubscriberById(int id)
        {
            var db = GetDB();
            var data = db.Subcribers.Single(m => m.ID == id);
            return data;
        }

        public static CommonSubscriberReg GetRegisterFormById(int id)
        {
            var db = GetDB();
            var data = db.CommonSubscriberRegs.Single(m => m.ID == id);
            return data;
        }

        public static bool EditRegisterForm(CommonSubscriberReg model, int myID)
        {
            var db = GetDB();

            var form = db.CommonSubscriberRegs.Single(m => m.ID == model.ID);
            if (form == null || (form.CommonUser != myID)) return false;

            form.FirstName = model.FirstName;
            form.MiddleName = model.MiddleName;
            form.Lastname = model.Lastname;
            form.Gender = model.Gender;
            form.Email = model.Email;
            form.ContactAddress = model.ContactAddress;
            form.LGACurrentID = model.LGACurrentID;
            form.LGAOriginID = model.LGAOriginID;
            form.DOB = model.DOB;
            form.CountryCityzenID = model.CountryCityzenID;
            form.CountryResidentID = model.CountryResidentID;
            form.MaritalStatus = model.MaritalStatus;
            form.Occupation = model.Occupation;
            form.BloodGroup = model.BloodGroup;
            form.PD_ContactNumber = model.PD_ContactNumber;
            form.PD_Name = model.PD_Name;
            form.NK_Address = model.NK_Address;
            form.NK_ContactNumber = model.NK_ContactNumber;
            form.NK_Name = model.NK_Name;

            db.SubmitChanges();

            return true;
        }

        public static bool DeleteRegterForm(int formID, int myID)
        {
            var db = GetDB();

            var form = db.CommonSubscriberRegs.Single(m => m.ID == formID);
            if (form == null || (form.CommonUser != myID)) return false;

            var cardType = form.RegType;
            switch (cardType)
            {
                case 1: db.RegularSubscriberRegs.DeleteOnSubmit(db.RegularSubscriberRegs.Single(m => m.ID == form.ID)); break;
                case 2: db.ProcessedSubscriberRegs.DeleteOnSubmit(db.ProcessedSubscriberRegs.Single(m => m.ID == form.ID)); break;
                case 3: db.SpecialSubscriberRegs.DeleteOnSubmit(db.SpecialSubscriberRegs.Single(m => m.ID == form.ID)); break;
                case 4: db.FirstClassCitizenRegs.DeleteOnSubmit(db.FirstClassCitizenRegs.Single(m => m.ID == form.ID)); break;
            }

            db.CommonSubscriberRegs.DeleteOnSubmit(form);

            db.SubmitChanges();

            return true;
        }

        public static bool EditRegisterForm(int formID, int myID, DateTime CommencementDate, DateTime IssueDate, DateTime ExpiredDate)
        {
            var db = GetDB();

            var form = db.CommonSubscriberRegs.Single(m => m.ID == formID);
            if (form == null || (form.CommonUser != myID) || (form.CommencementDate != null)) return false;

            form.CommencementDate = CommencementDate;
            form.IssuedDate = IssueDate;
            form.ExpiryDate = ExpiredDate;

            db.SubmitChanges();

            return true;
        }
    }
}